#!/usr/bin/env python
# -*- coding:utf-8 -*-
import sys
import csv
import json
import os
from ast import literal_eval
from pymongo import MongoClient
import pandas as pd 


def main(argv):
	conn = MongoClient('127.0.0.1', 27017)
	db = conn['forge-rcdb']
	# 数据库用户验证
	#db.authenticate("account", "password")
	my_set = db['configurator.materials']

	exlPath = r'C:\Users\L\Videos\python\srcTable'
	csvPath = r'C:\Users\L\Videos\python\csvTable'
	try:
		for x in os.listdir(exlPath):
			if os.path.isfile(os.path.join(exlPath,x)):
				print("excel file name: ", os.path.join(exlPath,x))
				data_xls = pd.read_excel(os.path.join(exlPath,x), index_col = 0)
				newCsvFilePath =  csvPath+'\\'+os.path.splitext(x)[0]+'.csv'
				data_xls.to_csv(newCsvFilePath, encoding='utf-8')
			else:
				print("file Name ", x, "is not excel file")

		for y in os.listdir(csvPath):
			if os.path.isfile(os.path.join(csvPath,y)):
				print("csv file name: ", os.path.join(csvPath,y))
				read_csv(csvPath,y,my_set)
			else:
				print("file Name ", y, "is not excel file")
	except Exception as e:
		print(e)


def read_csv(Path, file, my_set):
	try:
		#csv文件检查，非法行删除
		csvSrcfile = open(os.path.join(Path,file), 'r',encoding="utf-8")
		csvNewfile = open(Path+'\\new_'+file, 'w',encoding="utf-8")
		csvNewWtr  = csv.writer(csvNewfile) 

		for line in csv.reader(csvSrcfile) :
			flag=True
			#发现有空字段，undefine 总计等字样的行 不写入新文件
			for z in range(len(line)):
				if line[z] == '':
					print("line", z, "is empty")
					flag = False
				if line[z].find("总计") != -1:
					print("line", line[z], " is a aggregate row")
					flag = False
				if line[z].find("Unnamed: ") != -1:
					print("line", line[z], " is not a effective row")
					flag = False
			if flag:
				csvNewWtr.writerow(line)
			else:
				print("skip this line")
		csvSrcfile.close() 
		csvNewfile.close() 
		os.remove(os.path.join(Path,file))
		os.rename(Path+'\\new_'+file, os.path.join(Path,file))

		with open(os.path.join(Path,file), 'r', encoding="utf-8") as csvfile:
			insrReader = csv.DictReader(csvfile)
			title = insrReader.fieldnames

			for row in insrReader:
				if not row['单价'].isdigit():
					print("record ",row['单价']," is not a number")
					continue;

				if row['单位']!='个' and row['单位']!='m' and row['单位']!='m2' and row['单位']!='m3' and row['单位']!='kg':
					print("record ",row['单位']," is not effective unit")
					continue;

				#查重
				if my_set.find({ "name":row[title[0]] }).count() != 0:
					print("record ",row[title[0]]," has been added!!!")
					continue;

				users={
					"name" : row[title[0]], 
					"supplier" : "思行材料", 
					"currency" : row['单位'], 
					"price" : 'NumberInt('+row['单价']+')'
				}
				my_set.insert(users) 
	except Exception as e:
		print(e)

if __name__ == "__main__":
	main(sys.argv[1:])